﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        /// <summary>
        /// 插入邮件信息
        /// </summary>
        /// <returns></returns>
        public int InsertMsgBox(MsgBoxInfo msgBoxInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.UserUid),
                                      DbHelper.MakeInParam("@msgid",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.MsgId),
                                      DbHelper.MakeInParam("@msgtype",(DbType)SqlDbType.NChar,10,msgBoxInfo.MsgType),
                                      DbHelper.MakeInParam("@from",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.From),                                      
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.Title),
                                      DbHelper.MakeInParam("@addressee",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.Addressee),
                                      DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,msgBoxInfo.Content.Length,msgBoxInfo.Content),
                                      DbHelper.MakeInParam("@read",(DbType)SqlDbType.Bit,5,msgBoxInfo.Read),
                                      DbHelper.MakeInParam("@reply",(DbType)SqlDbType.Bit,5,msgBoxInfo.Reply),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,msgBoxInfo.Date.Length,msgBoxInfo.Date)                                    
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("INSERT INTO [ZhangBY].[dbo].[{0}messagebox] ", BaseConfigs.GetTablePrefix);
            commantText.Append("([msgid],[userid],[addressee],[from],[msgtype],[title],[content],[Read],[Reply],[date])");
            commantText.Append("VALUES(@msgid,@userid,@addressee,@from,@msgtype,@title,@content,@read,@reply,@date)");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 根据用户ID和msgid更新邮件信息
        /// </summary>
        /// <param name="msgBoxInfo"></param>
        /// <returns></returns>
        public int UpdateMsgBoxByMsgIdAndUserID(MsgBoxInfo msgBoxInfo)
        {

            DbParameter[] parms = {
                                       DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.UserUid),
                                      DbHelper.MakeInParam("@msgid",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.MsgId),
                                      DbHelper.MakeInParam("@msgtype",(DbType)SqlDbType.NChar,10,msgBoxInfo.MsgType),
                                      DbHelper.MakeInParam("@from",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.From),                                      
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.Title),
                                      DbHelper.MakeInParam("@addressee",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.Addressee),
                                      DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,msgBoxInfo.Content.Length,msgBoxInfo.Content),
                                      DbHelper.MakeInParam("@read",(DbType)SqlDbType.Bit,5,msgBoxInfo.Read),
                                      DbHelper.MakeInParam("@reply",(DbType)SqlDbType.Bit,5,msgBoxInfo.Reply),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,msgBoxInfo.Date.Length,msgBoxInfo.Date)                                     
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}messagebox] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET [addressee]=@addressee,[from]=@from,[msgtype]=@msgtype,[title]=@title,[content]=@content,[Read]=@read,[Reply]=@reply,[date]=@date");
            commantText.Append(" WHERE [msgid]=@msgid AND[userid]=@userid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }

        /// <summary>
        /// 根据msgid更新邮件信息
        /// </summary>
        /// <param name="msgBoxInfo"></param>
        /// <returns></returns>
        public int UpdateMsgBoxByMsgId(MsgBoxInfo msgBoxInfo)
        {

            DbParameter[] parms = {
                                       DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.UserUid),
                                      DbHelper.MakeInParam("@msgid",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.MsgId),
                                      DbHelper.MakeInParam("@msgtype",(DbType)SqlDbType.NChar,10,msgBoxInfo.MsgType),
                                      DbHelper.MakeInParam("@from",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.From),                                      
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.Title),
                                      DbHelper.MakeInParam("@addressee",(DbType)SqlDbType.NVarChar,60,msgBoxInfo.Addressee),
                                      DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,msgBoxInfo.Content.Length,msgBoxInfo.Content),
                                      DbHelper.MakeInParam("@read",(DbType)SqlDbType.Bit,5,msgBoxInfo.Read),
                                      DbHelper.MakeInParam("@reply",(DbType)SqlDbType.Bit,5,msgBoxInfo.Reply),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,msgBoxInfo.Date.Length,msgBoxInfo.Date)                                       
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}messagebox] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET [addressee]=@addressee,[from]=@from,[msgtype]=@msgtype,[title]=@title,[content]=@content,[Read]=@read,[Reply]=@reply,[date]=@date");
            commantText.Append(" WHERE [msgid]=@msgid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 更新信息阅读状态
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="msgid"></param>
        /// <param name="read"></param>
        /// <returns></returns>
        public int UpdateMsgBoxReadByUseIdAndMsgId(string userid, string msgid,bool read)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@msgid",(DbType)SqlDbType.NVarChar,60,msgid),
                                      DbHelper.MakeInParam("@read",(DbType)SqlDbType.Bit,4,read)
                                    
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}messagebox] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[Read]=@read");
            commantText.Append(" WHERE [msgid]=@msgid AND [userid]=@userid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 更新信息阅读状态
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="msgid"></param>
        /// <param name="read"></param>
        /// <returns></returns>
        public int UpdateMsgBoxReadByMsgId(string userid, string msgid, bool read)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@msgid",(DbType)SqlDbType.NVarChar,60,msgid),
                                      DbHelper.MakeInParam("@read",(DbType)SqlDbType.Bit,4,read)
                                    
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}messagebox] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[Read]=@read");
            commantText.Append(" WHERE [msgid]=@msgid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 更新信息回复状态
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="msgid"></param>
        /// <param name="reply"></param>
        /// <returns></returns>
        public int UpdateMsgBoxReplyByUseIdAndMsgId(string userid, string msgid, bool reply)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@msgid",(DbType)SqlDbType.NVarChar,60,msgid),
                                      DbHelper.MakeInParam("@reply",(DbType)SqlDbType.Bit,4,reply)
                                    
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}messagebox] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[reply]=@reply");
            commantText.Append(" WHERE [msgid]=@msgid AND [userid]=@userid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 更新信息回复状态
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="msgid"></param>
        /// <param name="read"></param>
        /// <returns></returns>
        public int UpdateMsgBoxReplyByMsgId(string userid, string msgid, bool reply)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@msgid",(DbType)SqlDbType.NVarChar,60,msgid),
                                      DbHelper.MakeInParam("@reply",(DbType)SqlDbType.Bit,4,reply)
                                    
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}messagebox] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[Reply]=@reply");
            commantText.Append(" WHERE [msgid]=@msgid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }

        /// <summary>
        /// 根据USERID和msg查找邮件信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="msgid"></param>
        /// <returns></returns>
        public DataTable GetMsgBoxByProIdAndUserId(string userid, string msgid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@msgid",(DbType)SqlDbType.NVarChar,60,msgid)
                                  };
            string commandText = string.Format("SELECT * FROM [dbo].[{0}messagebox] WHERE [userid]=@userid AND [msgid]=@msgid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据msgid查找邮件信息
        /// </summary>
        /// <param name="msgid"></param>
        /// <returns></returns>
        public DataTable GetMsgBoxByProId(string msgid)
        {
            DbParameter parms = DbHelper.MakeInParam("@msgid", (DbType)SqlDbType.NVarChar, 60, msgid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}messagebox] WHERE [msgid]=@msgid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据USERID读取所有邮件数据行
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetMsgBoxByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}messagebox] WHERE [userid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 根据USERID和msgid删除邮件信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="msgid"></param>
        /// <returns></returns>
        public int DelMsgBoxByUserIdAndProId(string userid, string msgid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@msgid",(DbType)SqlDbType.NVarChar,60,msgid)
                                  };
            string commandText = string.Format("DELETE FROM [dbo].[{0}messagebox] WHERE [userid]=@userid AND [msgid]=@msgid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据msgid删除邮件信息
        /// </summary>
        /// <param name="msgid"></param>
        /// <returns></returns>
        public int DelMsgBoxByProId(string msgid)
        {
            DbParameter parms = DbHelper.MakeInParam("@msgid", (DbType)SqlDbType.NVarChar, 60, msgid);
            string commandText = string.Format("DELETE FROM [dbo].[{0}messagebox] WHERE [msgid]=@msgid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据用户ID取得邮件产品总行数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountMsgBoxByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT COUNT(msgid) FROM [dbo].[{0}messagebox] WHERE [userid]=@userid", BaseConfigs.GetTablePrefix);
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }
    }
}
